package com.hillinsight.service;

import com.hillinsight.entity.HiveColumns;
import com.hillinsight.entity.HiveTBLS;
import com.hillinsight.entity.MySQLColumns;
import com.hillinsight.entity.MySQLTables;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * Created by fangzheng on 2018/9/5.
 */
@Service
public class HiveService {

    @Autowired
    @Qualifier("hiveJdbcTemplate")
    private JdbcTemplate jdbcTemplate;

    private String getDbName(){
        String database_name = null;
        try {
            database_name = jdbcTemplate.getDataSource().getConnection().getCatalog();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return database_name;
    }

    /**
     * 所有数据库
     * @param sql
     * @return
     */
//    public List getAllDatabases(String sql){
//        List databases = new ArrayList();
//        List rows = jdbcTemplate.queryForList("SELECT TABLE_SCHEMA FROM information_schema.TABLES GROUP BY TABLE_SCHEMA;");
//        Iterator it = rows.iterator();
//        while(it.hasNext()) {
//            Map userMap = (Map) it.next();
//            databases.add(userMap.get("TABLE_SCHEMA"));
//        }
//        return databases;
//    }

    /**
     * 当前连接的数据库
     * 也可以通过SELECT database();来获取当前连接的数据库名称
     * @return
     */
    public List getAllDatabases(){
        List databases = new ArrayList();
        List rows = jdbcTemplate.queryForList("SELECT TABLE_SCHEMA FROM information_schema.TABLES WHERE TABLE_SCHEMA = '"+getDbName()+"' GROUP BY TABLE_SCHEMA;");
        Iterator it = rows.iterator();
        while(it.hasNext()) {
            Map userMap = (Map) it.next();
            databases.add(userMap.get("TABLE_SCHEMA"));
        }
        return databases;
    }

    public List<HiveTBLS> getAllTables(){
        String sql = "SELECT * FROM "+getDbName()+".TBLS;";
        return jdbcTemplate.query(sql,new HiveTBLSRowMapper());
    }

    public List<HiveColumns> getAllColumns(String table_name){
        String sql = "SELECT tb.TBL_NAME,col.*\n" +
                "FROM COLUMNS_V2 col\n" +
                "LEFT JOIN TBLS tb\n" +
                "  on col.CD_ID=tb.TBL_ID\n" +
                "WHERE tb.TBL_NAME='"+table_name+"' ORDER BY col.INTEGER_IDX;";
        return jdbcTemplate.query(sql,new HiveColumnsRowMapper());
    }

    /**
     * HiveTBLS ：hive-metadata对应的所有表
     */
    class HiveTBLSRowMapper implements RowMapper<HiveTBLS> {
        //rs为返回结果集，以每行为单位封装着
        public HiveTBLS mapRow(ResultSet rs, int rowNum) throws SQLException {
            HiveTBLS table = new HiveTBLS();
            table.setTBL_ID(rs.getString("TBL_ID"));
            table.setCREATE_TIME(rs.getString("CREATE_TIME"));
            table.setDB_ID(rs.getString("DB_ID"));
            table.setLAST_ACCESS_TIME(rs.getString("LAST_ACCESS_TIME"));
            table.setOWNER(rs.getString("OWNER"));
            table.setRETENTION(rs.getString("RETENTION"));
            table.setSD_ID(rs.getString("SD_ID"));
            table.setTBL_NAME(rs.getString("TBL_NAME"));
            table.setTBL_TYPE(rs.getString("TBL_TYPE"));
            table.setVIEW_EXPANDED_TEXT(rs.getString("VIEW_EXPANDED_TEXT"));
            table.setVIEW_ORIGINAL_TEXT(rs.getString("VIEW_ORIGINAL_TEXT"));
            return table;
        }
    }

    /**
     * HiveColumns ： hive-metadata中表对应的所有列
     */
    class HiveColumnsRowMapper implements RowMapper<HiveColumns> {
        //rs为返回结果集，以每行为单位封装着
        public HiveColumns mapRow(ResultSet rs, int rowNum) throws SQLException {
            HiveColumns table = new HiveColumns();
            table.setCD_ID(rs.getString("CD_ID"));
            table.setCOMMENT(rs.getString("COMMENT"));
            table.setCOLUMN_NAME(rs.getString("COLUMN_NAME"));
            table.setTYPE_NAME(rs.getString("TYPE_NAME"));
            table.setINTEGER_IDX(rs.getString("INTEGER_IDX"));
            return table;
        }
    }

}
